/**
 * 
 */
package com.base.myproject.server.excel;

import java.io.File;
import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

import com.base.myproject.server.database.PoolMan;

/**
 * 
 * 指定格式的excel导入到数据库
 * @author Administrator
 *
 */
public class Excel2DB {

	String filename ;
	String tablename ;
	Cell[] columns;
	Cell[] types;
	Cell[] codename;
	int columnscount;
	int rows;
	
	public Excel2DB(String filename)
	{
		this.filename = filename;
	}
	
	int success = 0;
	public int Import() throws BiffException, IOException, SQLException
	{
		
		Workbook workbook = Workbook.getWorkbook(new File(filename));
		Sheet sheet = workbook.getSheet(0);
		columnscount = sheet.getColumns() ;
		rows = sheet.getRows();
		tablename = sheet.getCell(0, 0).getContents();  //表名
		columns = sheet.getRow(1);  //字段名
		types = sheet.getRow(2);  //数据类型，用来设置PreparedStatement的参数，现在定义为int,double和其它类型三种
		codename = sheet.getRow(3);  //这一种现在没作用，用来提示用户输入的数据的而已
		PoolMan pm = new PoolMan();
		String insertsql = "insert into "+tablename +"  (";
		String values = "";
		for(int i = 0 ; i<columns.length;i++)
		{
			insertsql +=columns[i].getContents()+",";
			values +="?,";
		}
		insertsql = insertsql.substring(0, insertsql.length()-1);
		values = values.substring(0, values.length()-1);
		insertsql +=") values ("+values+")";
		
		System.out.println(insertsql);
		PreparedStatement ps =  pm.getConnection("test").prepareStatement(insertsql);
	
		for(int j = 4;j<rows;j++)
		{
			success++;
			ps.clearParameters();
			for(int k = 0 ; k<columns.length;k++)
			{
				if (types[k].getContents().equals("double")) {
					ps.setDouble(k+1, Double.parseDouble(sheet.getCell(k, j).getContents()));
				} else if (types[k].getContents().equals("int")) {
					ps.setInt(k+1, Integer.parseInt(sheet.getCell(k, j).getContents()));
				} else {
					ps.setString(k+1, sheet.getCell(k, j).getContents());
				}
			}
			ps.addBatch();
		}
		System.out.println(ps.executeBatch());
		
		workbook.close();
		
		return success;
	}
}
